﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using ToolBoxPro;
using System.IO;

/// <summary>
/// Summary description for Property
/// </summary>

namespace LinqObj
{
    public partial class Property
    {
        private EntityRef<Campaign> _Campaign;
        [Association(Storage = "_Campaign", OtherKey = "property_id",
        ThisKey = "property_id", IsForeignKey = true)]
        public Campaign Campaign
        {
            get
            {
                return this._Campaign.Entity;
            }
        }

        public string VendorStatusLabel
        {
            get
            {
                string _scheduleDate = FormContract.GetDateLabel(this.schedule_date);
                if (this.Vendor_Status1 != null)
                    return this.Vendor_Status1.vendor_status1 + " - " + _scheduleDate;
                return "";
            }
        }

        public string PropertyInfo
        {
            get
            {
                string retVal = "";
                retVal = this.address1;
                if (this.address2 != "" && this.address2 != null)
                    retVal = retVal + " " + this.address2;
                retVal = retVal + ", " + this.suburb + ", " + this.state + ", " + this.postcode;
                return retVal;
            }
        }

        public static Dictionary<int, string> alloatedStatusLookups;

        public static Dictionary<int, string> campaignStatusLookups;

        partial void OnCreated()
        {
            alloatedStatusLookups = (from a in _context.GetTable<Allocated_Status>()
                                     select a).ToDictionary(id => id.status_id, status => status.status);

            campaignStatusLookups = (from a in _context.GetTable<Campaign_Status>()
                                     select a).ToDictionary(id => id.status_id, status => status.status);
        }

        public string _AllocatedStatus
        {
            get
            {
                //return this.allocated_status.HasValue ? "":alloatedStatusLookups[this.allocated_status.Value];
                if (alloatedStatusLookups == null)
                {
                    alloatedStatusLookups = (from a in _context.GetTable<Allocated_Status>()
                                             select a).ToDictionary(id => id.status_id, status => status.status);
                }
                return alloatedStatusLookups[this.allocated_status];
            }
        }

        public string _CampaignStatus
        {
            get
            {
                if (campaignStatusLookups == null)
                {
                    campaignStatusLookups = (from a in _context.GetTable<Campaign_Status>()
                                             select a).ToDictionary(id => id.status_id, status => status.status);
                }
                //return this.campaign_status.HasValue ? "" : campaignStatusLookups[this.campaign_status.Value];
                return campaignStatusLookups[this.campaign_status];
            }
        }

        private static ToolboxDataContext _context = new ToolboxDataContext();

        public const string STATUS_ACTIVE = "active";
        public const string STATUS_INACTIVE = "inactive";
        public const string STATUS_ARCHIVED = "archived";

        public const string ALLOCATE_SAVED = "list_saved";
        public const string ALLOCATE_ORDERD = "ordered";
        public const string ALLOCATE_NOLIST = "nolist";

        //For Showing only
        public string PropertyStatusLabel
        {
            get
            {
                //switch (this._CampaignStatus.ToLower())
                //{
                //    case Property.STATUS_INACTIVE:
                //        return "Inactive";
                //    case Property.STATUS_ACTIVE:
                //        return "Active";
                //}

                //switch (this._AllocatedStatus.ToLower())
                //{
                //    case Property.ALLOCATE_SAVED:
                //        return "List Saved";
                //    case Property.ALLOCATE_ORDERD:
                //        return "Orders Sent";
                //}

                return this._CampaignStatus;
            }
        }

        public void setAgent(string strAgentID)
        {
            if (strAgentID == "0" || strAgentID == "")
            {
                this.Agent1 = null;
            }
            else
            {
                int agentID = Convert.ToInt32(strAgentID);
                this.Agent1 = (from _a in _context.GetTable<Agent>()
                               where _a.agent_id == agentID
                               select _a).First<Agent>();
            }
        }

        public void setListingAgent(string strAgentID)
        {
            if (strAgentID == "0" || strAgentID == "")
            {
                this.Agent2 = null;
            }
            else
            {
                int agentID = Convert.ToInt32(strAgentID);
                this.Agent2 = (from _a in _context.GetTable<Agent>()
                               where _a.agent_id == agentID
                               select _a).First<Agent>();
            }
        }

        public void setAdvAgent(string strAgentID)
        {
            if (strAgentID == "0" || strAgentID == "")
            {
                this.Agent = null;
            }
            else
            {
                int agentID = Convert.ToInt32(strAgentID);
                this.Agent = (from _a in _context.GetTable<Agent>()
                              where _a.agent_id == agentID
                              select _a).First<Agent>();
            }
        }

        public void setMangingAgent(string strAgentID)
        {
            if (strAgentID == "0" || strAgentID == "")
            {
                this.Agent3 = null;
            }
            else
            {
                int agentID = Convert.ToInt32(strAgentID);
                this.Agent3 = (from _a in _context.GetTable<Agent>()
                               where _a.agent_id == agentID
                               select _a).First<Agent>();
            }
        }

        public void setVendorStatus(string strVendorStatusID)
        {
            if (strVendorStatusID == "")
                this.Vendor_Status1 = null;
            else
            {
                int vendorStatusID = Convert.ToInt32(strVendorStatusID);

                if (this.vendor_status == vendorStatusID || vendorStatusID <= 0)
                    return;
                this.Vendor_Status1 = (from _a in _context.GetTable<Vendor_Status>()
                                       where _a.vendor_status_id == vendorStatusID
                                       select _a).First<Vendor_Status>();
            }
        }

        public void setAgentGroup(int agentGroupID)
        {
            if (this.agent_group_id == agentGroupID || this.agent_group_id <= 0)
                return;
            this.Agent_Group = (from _group in _context.GetTable<Agent_Group>()
                                where _group.agent_group_id == agentGroupID
                                select _group).SingleOrDefault();
        }

        public void setAgentID(int agentID)
        {
            if (this.Agent.agent_id == agentID)
                return;
            Agent _agent;
            _agent = (from _a in _context.GetTable<Agent>()
                      where _a.agent_id == agentID
                      select _a).First<Agent>();
            this.Agent = _agent;
        }

        //public void ClearAll()
        //{
        //    _Agent1 = default(EntityRef<Agent>);
        //    _Agent2 = default(EntityRef<Agent>);
        //    _Agent3 = default(EntityRef<Agent>);

        //    _Vendor_Status1 = default(EntityRef<Vendor_Status>);
        //    _Vendor_Detail = default(EntityRef<Vendor_Detail>);
        //    _Agent = default(EntityRef<Agent>);
        //}

        public static IEnumerable<Property> LoadPropertyList(int agentGroupID)
        {

            IEnumerable<Property> ret = from _property in _context.GetTable<Property>()
                                        where _property.agent_group_id == agentGroupID
                                        select _property;
            return ret.ToList();
        }

        public static Property GetProperty(int propertyID)
        {
            IEnumerable<Property> ret = from _property in _context.GetTable<Property>()
                                        where _property.property_id == propertyID
                                        select _property;
            return ret.SingleOrDefault();
        }

        public static Property GetProperty(int propertyID, int agentGroupID)
        {
            IEnumerable<Property> ret = from _property in _context.GetTable<Property>()
                                        where _property.property_id == propertyID &&
                                                _property.agent_group_id == agentGroupID
                                        select _property;
            return ret.SingleOrDefault();
        }

        public static Property GetPropertyByOrderID(int orderID)
        {

            Property retVal = (from _property in _context.GetTable<Property>()
                               where _property.property_id == (int?)(from _campaign in _context.GetTable<Campaign>()
                                                                     where _campaign.campaign_id == (int?)(from _order in _context.GetTable<Order>()
                                                                                                           where _order.order_id == orderID
                                                                                                           select _order.campaign_id).FirstOrDefault()
                                                                     select _campaign.property_id).FirstOrDefault()
                               select _property).FirstOrDefault();
            return retVal;
        }


        public static void Insert_Update(Property property)
        {
            if (property.property_id <= 0)
            {
                if (property.allocated_status == 0)
                    property.SetAllocatedStatus(Property.ALLOCATE_NOLIST);
                property.created_date = DateTime.Now;
                _context.GetTable<Property>().InsertOnSubmit(property);
            }

            try
            {
                property.last_modified_date = DateTime.Now;
                _context.SubmitChanges();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                _context.ChangeConflicts.Clear();
                _context.SubmitChanges();
            }
        }

        public void SetAllocatedStatus(string strAllocated)
        {
            int? key = FormContract.FindKey(alloatedStatusLookups, strAllocated);
            if (!key.HasValue)
                throw new Exception("the value is not found in the AllocatedStatusLookups");
            else
                SetAllocatedStatus(key.Value);
        }

        public void SetAllocatedStatus(int allocated)
        {
            if (allocated == this.allocated_status || allocated <= 0)
                return;
            this.Allocated_Status1 = (from a in _context.GetTable<Allocated_Status>()
                                      where a.status_id == allocated
                                      select a).SingleOrDefault();
        }

        public void SetCampaignStatus(string strCampaignStatus)
        {
            int? key = FormContract.FindKey(campaignStatusLookups, strCampaignStatus);
            if (!key.HasValue)
                throw new Exception("the value is not found in CampaignStatusLookups");
            else
                SetCampaignStatus(key.Value);
        }

        public void SetCampaignStatus(int _campaignStatus)
        {
            if (_campaignStatus == this.campaign_status || _campaignStatus <= 0)
                return;
            this.Campaign_Status1 = (from a in _context.GetTable<Campaign_Status>()
                                     where a.status_id == _campaignStatus
                                     select a).SingleOrDefault();
        }

        public static void SubmitChanges()
        {
            try
            {
                _context.SubmitChanges();
            }
            catch (Exception ex)
            {
                _context.ChangeConflicts.Clear();
                _context.SubmitChanges();
            }
        }

        public void CreateDir()
        {
            string _path = System.AppDomain.CurrentDomain.BaseDirectory;
            string dir = _path + this.Agent_Group.agent_group_name +
                        "\\" + this.property_id + " " + this.address1 + " " + this.address2 + " " + this.suburb;

            CreateDir(dir);
        }

        public void CreateDir(string path)
        {
            DirectoryInfo propertyaddress = Directory.CreateDirectory(path);
            DirectoryInfo waitingforapproval = propertyaddress.CreateSubdirectory("Waiting for approval");
            DirectoryInfo approved = propertyaddress.CreateSubdirectory("Approved");
            DirectoryInfo UploadedFiles = propertyaddress.CreateSubdirectory("UploadedFiles");
            waitingforapproval.CreateSubdirectory("board");
            waitingforapproval.CreateSubdirectory("brochures");
            waitingforapproval.CreateSubdirectory("copy writing");
            waitingforapproval.CreateSubdirectory("Floor Plan");
            waitingforapproval.CreateSubdirectory("misc");
            waitingforapproval.CreateSubdirectory("photos");

            approved.CreateSubdirectory("board");
            approved.CreateSubdirectory("brochures");
            approved.CreateSubdirectory("copy writing");
            approved.CreateSubdirectory("Floor Plan");
            approved.CreateSubdirectory("misc");
            approved.CreateSubdirectory("photos");
        }
    }
}

//#region
//private static bool shareWithOtherAgent;
//private static List<Property> propertyList;
//private int propertyID;
//private string address1;
//private string address2;
//private string state;
//private string suburb;
//private string postcode;
//private string allocatedStatus;
//private string campaignStatus;
////private List<AgentRole> agentList;
//private int agentID;
//private int agentGroupID;
//private int listingAgent;
//private int advertisingAgent;
//private int managingAgent;
//private int vendorStatus;
//private DateTime scheduleDate;
//private string propertyLabel;
//private DateTime lastModifiedDate;
//private DateTime createdDate;
//private static IDictionary<int, string> vendorStatusLookup;
//#endregion

//#region Property
//public DateTime LastModifiedDate { get { return this.lastModifiedDate; } set { this.lastModifiedDate = value; } }
//public DateTime CreatedDate { get { return this.createdDate; } set { this.createdDate = value; } }
//public int PropertyID { get { return this.propertyID; } set { this.propertyID = value; } }
//public String State { get { return this.state; } set { this.state = value; } }
//public String Address1 { get { return this.address1; } set { this.address1 = value; } }
//public String Address2 { get { return this.address2; } set { this.address2 = value; } }
//public String PropertyStatus { get { return this.allocatedStatus; } set { this.allocatedStatus = value; } }

/*
 * If it is Closed, List Saved, Orders Sent or Wait for Approve
 */
//        public string PropertyStatusLabel
//        {
//            get
//            {
//                switch (this.allocatedStatus)
//                {
//                    case Property.STATUS_CLOSE:
//                        return "Closed";
//                    case Property.STATUS_LIST_SAVED:
//                        return "List Saved";
//                    case Property.STATUS_ORDERD:
//                        return "Orders Sent";
//                    case Property.STATUS_PROPOSE:
//                        return "Wait for Approve";
//                    case Property.STATUS_ACTIVE:
//                        return "Active";
//                    default:
//                        return null;
//                }
//            }
//        }
//        public String CampaignStatus { get { return this.campaignStatus; } set { this.campaignStatus = value; } }
//        public string Suburb { get { return this.suburb; } set { this.suburb = value; } }
//        public string Postcode { get { return this.postcode; } set { this.postcode = value; } }
//        public int AgentID { get { return this.agentID; } set { this.agentID = value; } }
//        public int AgentGroupID { get { return this.agentGroupID; } set { this.agentGroupID = value; } }
//        public int ListingAgent { get { return this.listingAgent; } set { this.listingAgent = value; } }
//        public int AdvertisingAgent { get { return this.advertisingAgent; } set { this.advertisingAgent = value; } }
//        public int ManagingAgent { get { return this.managingAgent; } set { this.managingAgent = value; } }
//        public int VendorStatus { get { return this.vendorStatus; } set { this.vendorStatus = value; } }
//        public DateTime ScheduleDate { get { return this.scheduleDate; } set { this.scheduleDate = value; } }

//        public const string STATUS_ACTIVE = "active";
//        public const string STATUS_CLOSE = "inactive";
//        public const string STATUS_PROPOSE = "propse";
//        public const string STATUS_LIST_SAVED = "list_saved";
//        public const string STATUS_ORDERD = "orderd";

//        /*
//         * Property Location
//         */ 
//        public string PropertyInfo
//        {
//            get
//            {
//                string retVal = "";
//                retVal = this.address1;
//                if (this.address2 != "" && this.address2 != null)
//                    retVal = retVal + ", " + this.address2;
//                retVal = retVal + ", " + this.suburb + ", " + this.state + ", " + this.postcode;
//                return retVal;
//            }
//        }


//        public static void Property_ListSaved(IDbConnection conn, int propertyID)
//        {
//            setShoppingList(conn, propertyID, Property.STATUS_LIST_SAVED);
//        }

//        public static void Property_Closed(IDbConnection conn, int propertyID)
//        {
//            setShoppingList(conn, propertyID, Property.STATUS_CLOSE);
//        }

//        public static void Property_Ordered(IDbConnection conn, int propertyID)
//        {
//            setShoppingList(conn, propertyID, Property.STATUS_ORDERD);
//        }

//        private static void setShoppingList(IDbConnection conn, int propertyID, string allocatedStatus)
//        {
//            string update_sql_script = @"update [property]
//                                        set allocated_status = @allocatedStatus
//                                            where property_id = @propertyID";
//            IDbTransaction tran = conn.BeginTransaction();
//            try
//            {
//                IDbCommand cmd = conn.CreateCommand();
//                IDbDataParameter param = cmd.CreateParameter();
//                param.ParameterName = "@allocatedStatus";
//                param.DbType = DbType.String;
//                param.Value = allocatedStatus;
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@propertyID";
//                param.DbType = DbType.Int32;
//                param.Value = propertyID;
//                cmd.Parameters.Add(param);

//                cmd.Transaction = tran;


//                cmd.CommandText = update_sql_script;
//                cmd.ExecuteNonQuery();
//                tran.Commit();
//            }
//            catch (Exception error)
//            {
//                tran.Rollback();
//                throw (error);
//            }
//        }

//        /*
//         * Sale Method
//         */ 
//        public string VendorStatusLabel
//        {
//            get
//            {
//                if (Property.vendorStatusLookup != null)
//                {
//                    string _scheduleDate = FormContract.GetDateLabel(this.scheduleDate);
//                    if (this.ScheduleDate == null)
//                        _scheduleDate = "";
//                    string propertyVendor = vendorStatusLookup[this.VendorStatus] + " - " + _scheduleDate;
//                    return propertyVendor;
//                }
//                return null;
//            }
//        }
//        #endregion

//        public Property() { }

//        public static void SetVendorStatusLookups(IDictionary<int, string> lookups)
//        {
//            Property.vendorStatusLookup = lookups;
//        }

//        public static Property getProperty(IDbConnection conn, int propertyID)
//        {
//            string sql_script = @"select address1, address2, suburb
//                            ,property_id,
//                            campaign_status, allocated_status, postcode, agent_group_id, listing_agent, 
//                            advertising_agent, managing_agent, vendor_status, schedule_date,
//                            last_modified_date, created_date
//                            from property where property_id = " + propertyID;

//            IDbCommand cmd = conn.CreateCommand();
//            cmd.CommandText = sql_script;

//            IDataReader dr = cmd.ExecuteReader();
//            Property property = new Property();

//            if (dr.Read())
//            {
//                property.address1 = dr.GetString(0);
//                property.address2 = dr.GetString(1);
//                property.suburb = dr.GetString(2);
//                property.propertyID = dr.GetInt32(3);
//                if (!dr.IsDBNull(4))
//                    property.campaignStatus = dr.GetString(4);
//                if (!dr.IsDBNull(5))
//                    property.allocatedStatus = dr.GetString(5);
//                if (!dr.IsDBNull(6))
//                    property.postcode = dr.GetString(6);

//                if (!dr.IsDBNull(7)) //  postcode, listing_agent, advertising_agent, managing_agent, sale_method
//                    property.AgentGroupID = dr.GetInt32(7);
//                if (!dr.IsDBNull(8))
//                    property.ListingAgent = dr.GetInt32(8);
//                if (!dr.IsDBNull(9))
//                    property.advertisingAgent = dr.GetInt32(9);
//                if (!dr.IsDBNull(10))
//                    property.managingAgent = dr.GetInt32(10);
//                if (!dr.IsDBNull(11))
//                    property.VendorStatus = dr.GetInt32(11);
//                if (!dr.IsDBNull(12))
//                    property.scheduleDate = dr.GetDateTime(12);
//                if (!dr.IsDBNull(13))
//                    property.lastModifiedDate = dr.GetDateTime(13);
//                if (!dr.IsDBNull(14))
//                    property.createdDate = dr.GetDateTime(14);
//            }

//            return property;
//        }

//        public static bool SaveProperty(IDbConnection conn, Property property)
//        {
//            string insert_sql_script =
//                            @"insert into property(address1, address2, suburb, state, postcode, agent_id, campaign_status,
//                                                vendor_status, listing_agent, advertising_agent, managing_agent, schedule_date)
//                                values(@address1, @address2, @suburb, @state, @postcode, @agentID, @campaignStatus
//                                                @vendorStatus, @listingAgent, @advertisingAgent, @managingAgent, @scheduleDate);";
//            string update_sql_script = @"update [property]
//                                        set address1 = @address1,
//                                                address2 = @address2, suburb = @suburb, 
//                                                state = @state, postcode = @postcode, agent_id = @agentID, 
//                                                campaign_status = @campaignStatus, vendor_status = @vendorStatus, 
//                                                listing_agent = @listingAgent, advertising_agent = @advertisingAgent, 
//                                                managing_agent = @managingAgent, schedule_date = @scheduleDate
//                                            where property_id = @propertyID";
//            IDbTransaction tran = conn.BeginTransaction();
//            try
//            {
//                IDbCommand cmd = conn.CreateCommand();
//                IDbDataParameter param = cmd.CreateParameter();
//                param.ParameterName = "@address1";
//                param.DbType = DbType.String;
//                param.Value = property.Address1;
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@address2";
//                param.DbType = DbType.String;
//                param.Value = property.Address2;
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.DbType = DbType.String;
//                param.ParameterName = "@suburb";
//                param.Value = SetDbValue(property.Suburb);
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.DbType = DbType.String;
//                param.ParameterName = "@state";
//                param.Value = SetDbValue(property.State);
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@postcode";
//                param.DbType = DbType.String;
//                param.Value = SetDbValue(property.Postcode);
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@agentID";
//                param.Value = property.AgentID;
//                param.DbType = DbType.Int32;
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@campaignStatus";
//                param.DbType = DbType.String;
//                param.Value = SetDbValue(property.campaignStatus);

//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@vendorStatus";
//                param.DbType = DbType.String;
//                param.Value = SetDbValue(property.vendorStatus);
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@advertisingAgent";
//                param.Value = SetDbValue(property.advertisingAgent);
//                param.DbType = DbType.Int32;
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@managingAgent";
//                param.Value = SetDbValue(property.managingAgent);
//                param.DbType = DbType.Int32;
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@listingAgent";
//                param.Value = SetDbValue(property.listingAgent);
//                param.DbType = DbType.Int32;
//                cmd.Parameters.Add(param);

//                param = cmd.CreateParameter();
//                param.ParameterName = "@scheduleDate";
//                param.Value = SetDbValue(property.scheduleDate);
//                param.DbType = DbType.Date;
//                cmd.Parameters.Add(param);

//                cmd.Transaction = tran;

//                if (property.propertyID > 0)
//                {
//                    param = cmd.CreateParameter();
//                    param.ParameterName = "@propertyID";
//                    param.Value = property.propertyID;
//                    param.DbType = DbType.Int32;
//                    cmd.Parameters.Add(param);
//                    cmd.CommandText = update_sql_script;
//                    cmd.ExecuteNonQuery();
//                }
//                else
//                {
//                    cmd.CommandText = insert_sql_script;
//                    cmd.ExecuteNonQuery();
//                    cmd.CommandText = @"select IDENT_CURRENT('property') as PropertyID";
//                    property.PropertyID = Convert.ToInt32(cmd.ExecuteScalar().ToString());
//                }

//                tran.Commit();
//                if (property.PropertyID > 0)
//                    return true;
//                return false;
//            }
//            catch (Exception error)
//            {
//                tran.Rollback();
//                throw (error);
//            }

//        }

//        public static IDictionary<int, string> getSaleMethod(IDbConnection conn)
//        {

//            string sql_script =
//                                @"select * from Vendor_Status";


//            IDbCommand cmd = conn.CreateCommand();
//            cmd.CommandText = sql_script;

//            IDataReader dr = cmd.ExecuteReader();
//            IDictionary<int, string> saleMethod = new Dictionary<int, string>();
//            saleMethod.Add(new KeyValuePair<int, string>(0, ""));
//            while (dr.Read())
//            {
//                int key = Convert.ToInt32(dr["vendor_status_id"].ToString());
//                string value = dr["vendor_status"].ToString();
//                saleMethod.Add(key, value);
//            }
//            return saleMethod;

//        }

//        public static IEnumerable<Property> LoadPropertyList(IDbConnection conn, int agentGrougRef)
//        {
//            string sql_script =
//                                @"select address1, address2, suburb
//                            ,property_id,
//                            campaign_status, allocated_status, postcode, listing_agent, advertising_agent, managing_agent, vendor_status,
//                                schedule_date,
//                            last_modified_date, created_date
//                            from property
//                            where agent_group_id = @agentGroupRef";


//            IDbCommand cmd = conn.CreateCommand();
//            cmd.CommandText = sql_script;

//            IDbDataParameter param = cmd.CreateParameter();
//            param.ParameterName = "@agentGroupRef";
//            param.DbType = DbType.Int32;
//            param.Value = agentGrougRef;
//            cmd.Parameters.Add(param);

//            IDataReader dr = cmd.ExecuteReader();
//            propertyList = new List<Property>();

//            while (dr.Read())
//            {
//                Property property = new Property();

//                property.address1 = dr.GetString(0);
//                property.address2 = dr.GetString(1);
//                property.suburb = dr.GetString(2);
//                property.propertyID = dr.GetInt32(3);
//                if (!dr.IsDBNull(4))
//                    property.campaignStatus = dr.GetString(4);
//                if (!dr.IsDBNull(5))
//                    property.allocatedStatus = dr.GetString(5);
//                property.postcode = dr.GetString(6);
//                //listing_agent, advertising_agent, managing_agent, vendor_status,                                
//                //schedule_date,
//                //            last_modified_date, created_date

//                if (!dr.IsDBNull(10))
//                    property.vendorStatus = dr.GetInt32(10);
//                if (!dr.IsDBNull(11))
//                    property.scheduleDate = dr.GetDateTime(11);
//                if (!dr.IsDBNull(12))
//                    property.lastModifiedDate = dr.GetDateTime(12);
//                if (!dr.IsDBNull(13))
//                    property.createdDate = dr.GetDateTime(13);
//                propertyList.Add(property);

//            }
//            return propertyList;

//        }
//    }
